﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MYSQL_db.Model;
using System.Data;
using MySql.Data.MySqlClient;

namespace MYSQL_db.DAL
{
    class ConsumeDAL
    {

        public static int updatePoints(int v, string memcardid)
        {
            return
            SqlHelper.ExecuteNonQuery("update t_customer set points=points+@v where memcardid=@memcardid",
                new MySqlParameter("@memcardid", memcardid), new MySqlParameter("@v", v));

        }
        public static int insertPointLog(Consume consume)
        {
            return
            SqlHelper.ExecuteNonQuery(@"insert into t_member_points(memcardid,expense,points,type,date,operator,shopnum) 
                                                                values(@memcardid,@expense,@points,@type,NOW(),@operator,@shopnum)",
                new MySqlParameter("@memcardid", consume.MemCardID),
                new MySqlParameter("@expense", consume.Money),
                new MySqlParameter("@points", consume.Money),
                new MySqlParameter("@type", "增加"),
                new MySqlParameter("@operator", consume.Operator),
                new MySqlParameter("@shopnum", consume.ConsumeShopNum));
        }
        //把公共的代码封装到一个方法中，这样可以避免重复性的代码，提高代码复用性
        private static Consume ToConsume(DataRow row)
        {   //把从数据库中查询到的行中字段值，封装到对象中
            Consume consume = new Consume();
            consume.Id = (long)row["Id"];
            consume.MemCardID = (string)row["MemCardID"];
            consume.Money = (float)row["Money"];
            consume.Date = (DateTime)row["Date"];
            consume.Operator = (string)row["Operator"];
            consume.Type = (int)row["Type"];
            consume.ConsumeShopNum = (string)row["ConsumeShopNum"];
            return consume;
        }

        //通过MemCardID查询返回Consume对象
        public static Consume GetById(string MemCardID)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Consume where MemCardID=@MemCardID",
                new MySqlParameter("@MemCardID", MemCardID));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0]; //获取返回表中的第一行数据
                return ToConsume(row);    //将返回行中数据封装到Customer对象中
            }
        }
        //通过卡号删除
        public static int  DeleteById(string MemCardID)
        {
            return
            SqlHelper.ExecuteNonQuery("delete from T_Consume where MemCardID=@MemCardID",
                new MySqlParameter("@MemCardID", MemCardID));
        }
        //Customer对象作为参数，函数中取出属性值插入数据库
        public static int Insert(Consume consume)
        {
            return
            SqlHelper.ExecuteNonQuery(@"INSERT INTO T_Consume
                       (MemCardID
                       ,Money
                       ,Date
                       ,Operator
                        ,Type
                        ,ConsumeShopNum
                        )
                 Values(@MemCardID, @Money,@Date,@Operator,@Type,@ConsumeShopNum)",
                                              new MySqlParameter("@MemCardID", consume.MemCardID),
                                              new MySqlParameter("@Money", consume.Money),
                                              new MySqlParameter("@Date", consume.Date),
                                              new MySqlParameter("@Operator", consume.Operator),
                                              new MySqlParameter("@Type", consume.Type),
                                              new MySqlParameter("@ConsumeShopNum", consume.ConsumeShopNum)
                                               );
        }

        //传入一个Consume对象，提取对象属性，修改数据库值
        public static int Update(Consume consume)
        {
            return
            SqlHelper.ExecuteNonQuery(@"UPDATE T_Consume
                       SET MemCardID = @MemCardID
                          ,Money = @Money
                          ,Date = @Date
                          ,Operator = @Operator
                          ,Type = @Type
                          ,ConsumeShopNum=@ConsumeShopNum
                            WHERE MemCardID=@MemCardID",                                           
                                               new MySqlParameter("@MemCardID", consume.MemCardID),
                                               new MySqlParameter("@Money", consume.Money),
                                               new MySqlParameter("@Date", consume.Date),
                                               new MySqlParameter("@Operator", consume.Operator),
                                               new MySqlParameter("@Type", consume.Type),
                                               new MySqlParameter("@ConsumeShopNum", consume.ConsumeShopNum)
                               );
        }
        //无参数查询数据库，返回Consume对象数组
        public static Consume[] GetAll()
        {

            DataTable table = SqlHelper.ExecuteDataTable("select * from T_Consume");
            Consume[] consume = new Consume[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i]; //循环取出表中每一行

                consume[i] = ToConsume(row); //把取到的每一行加入对象数组
            }
            return consume; //返回对象数组
        }


        public static Consume[] GetByConsume(Consume ConObj)
        {

            DataTable table = SqlHelper.ExecuteDataTable("select * from T_Consume where MemCardID = "+ConObj.MemCardID);
            Consume[] consume = new Consume[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i]; //循环取出表中每一行

                consume[i] = ToConsume(row); //把取到的每一行加入对象数组
            }
            return consume; //返回对象数组
        }





        public static Consume[] Search(string sql, List<MySqlParameter> parameters)
        {
            DataTable table =
                SqlHelper.ExecuteDataTable(sql, parameters.ToArray());

            Consume[] consume = new Consume[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                consume[i] = ToConsume(table.Rows[i]);
            }
            return consume;
        }

        //接收table表，返回consume对象数组
        public static Consume[] Import(DataTable table)
        {        
            Consume[] consume = new Consume[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                consume[i] = ToConsume(table.Rows[i]);
            }
            return consume;
        }



    }
}
